import sys
import os
from osgeo import gdal
from osgeo import ogr
from multiprocessing import Pool, Process
from tqdm import tqdm
from time import time
import shutil
import geopandas
from sqlalchemy import create_engine 
from geopandas import GeoDataFrame
import psycopg2
import json

# 查询数据
def task_46():
	sql = """select folder
	from gykm_dir
	where gykm_type = '10km'
	and tile = '50SKA'
	"""
	con = psycopg2.connect(
		database="sentinel2",
		user="postgres",
		password="postgres",
		host="192.168.2.172",
		port="5432"
	)
	cur = con.cursor()
	cur.execute(sql)
	con.commit()
	data=cur.fetchall()
	paths =[
		os.path.join(record[0],'Image_Data','202109','L3A_TIFF','202109_3000')
		for record in data] 
	cur.close()
	con.close()
	for i in paths:print(i)
	return paths
# SQL创建矢量表
def task_47():
    # CREATE SEQUENCE id_seq_1 START WITH 1 INCREMENT BY 1 no minvalue no maxvalue cache 1;
	sql="""
 	CREATE  TABLE  IF  NOT  EXISTS  test_china_100km (
		id  INT4  NOT  NULL  DEFAULT nextval('id_seq_1'::regclass),
		geom  GEOMETRY(POLYGON,4326),
		gridcode  VARCHAR(255)  NOT  NULL,
		tile  VARCHAR(255)  NOT  NULL,
		PRIMARY  KEY (gridcode)
	);
	"""
	con = psycopg2.connect(
		database="sentinel2",
		user="postgres",
		password="postgres",
		host="192.168.2.172",
		port="5432")
	cur = con.cursor()
	cur.execute(sql)
	con.commit()
	cur.close()
	con.close()
	return
# SQL查询矢量表的坐标系
def task_48():
	sql="""select geom from china_100km;
	"""
	con = psycopg2.connect(
		database="sentinel2",
		user="postgres",
		password="postgres",
		host="192.168.2.172",
		port="5432")
	cur = con.cursor()
	cur.execute(sql)
	con.commit()
	data=cur.fetchall()
	cur.close()
	con.close()
	print(data)
	return
# 矢量文件导入pg库中
def task_49():
	table = ''
	if_exists = ''
	shpfile = r""
	url = "postgresql://postgres:postgres@192.168.2.172:5432/sentinel2"
	con = create_engine(url)
	geodataframe = geopandas.read_file(shpfile)
	geodataframe.to_postgis(name=table,con=con,if_exists=if_exists)
	return
# 创建视图
def task_50():
	sql = """CREATE VIEW current_employees AS  
	SELECT NAME, ID, SALARY 
	FROM EMPLOYEES;
	"""
	return
# gdal 连接pg数据库
def task_51():
	path="PG:host=192.168.2.172 port=5432 dbname='sentinel2' user='postgres' password='postgres' schema='public' table='china_100km'"

	gdal.AllRegister()
	ogr.RegisterAll()
	# dataset=gdal.Open(path,gdal.GA_ReadOnly)
	dataset=ogr.Open(path,gdal.GA_ReadOnly)
	GetRegistrar()
	print(dataset)
	return


if __name__ == "__main__":
    task_51()

